package com.cloud.salon.client.business.repository;

import com.cloud.salon.common.base.entity.BaseEntity;
import com.cloud.salon.common.base.entity.business.Record;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

/*
 * @Description 报表数据库查询仓库
 * @Author wolf--LZH
 * @CreateDate 2019/5/18 0018 下午 9:20
 */
public interface ReportRepository extends JpaRepository<BaseEntity, Integer> {

    /*
     * @Description 通过时间域获取订单趋势
     * @Author wolf--LZH
     */
    @Query(value = "SELECT date_format(date,'%Y-%m-%d') domain, COUNT(id) count, SUM(total_price) total "
            + "FROM record "
            + "WHERE date BETWEEN ?1 AND ?2 "
            + "GROUP BY domain", nativeQuery = true)
    List<Map<String, String>> consumeTrend(Date startDate, Date endDate);

    /*
     * @Description 获取会员与非会员的消费比例
     * @Author wolf--LZH
     */
    @Query(value = "SELECT !ISNULL(account_id) account, COUNT(ISNULL(account_id)) count "
            + "FROM record "
            + "WHERE date BETWEEN ?1 AND ?2 "
            + "GROUP BY account", nativeQuery = true)
    List<Map<String, String>> accountConsumeProportion(Date startDate, Date endDate);
    
    
    /*
     * @Description 查看部分项目在某时间域内的销售情况
     * @Author wolf--LZH
     */
    @Query(value = "SELECT date_format(date,'%Y-%m') domain, item_id item, COUNT(ri.id) count, ANY_VALUE(name) name "
            + "FROM record r "
            + "JOIN record_item ri ON ri.record_id = r.id "
            + "JOIN item i ON i.id = ri.item_id "
            + "WHERE date BETWEEN ?1 AND ?2 "
            + "AND item_id IN ?3 "
            + "GROUP BY domain, item "
            + "ORDER BY item, domain ", nativeQuery = true)
    List<Map<String, String>> itemConsumeTrend(Date startDate, Date endDate, List<Integer> itemIdList);
}
